# -*- coding: utf-8 -*-
"""
__mktime__ = '2019/10/16 0016'
__author__ = 'Administrator'
__filename__ = 'mqtt_sever'
文件注解：
文件版本：
"""



# -*- coding: utf-8 -*-
"""
__mktime__ = '2019/10/15 0015'
__author__ = 'Administrator'
__filename__ = 'pub'
文件注解：
文件版本：
"""
# !/usr/bin/env python
# encoding: utf-8
""" 
@version: v1.0 
@author: bb
@license: Apache Licence  
@contact: 44704708@qq.com 
@software: PyCharm 
@file: 
@time: 
@describe: mqtt 服务端
"""
import json
import sys
import os
import time
import datetime
import paho.mqtt.client as mqtt
import MySQLdb
import logging

sys.path.append(os.path.abspath(os.path.dirname(__file__) + '/' + '..'))
sys.path.append("..")

connect = None  # 连接对象
cursor = None  # 游标对象
id_val = 0
REPORT_TOPIC = 'one/#'  # 主题




def on_connect(client, userdata, flags, rc):
    logging.info('connected to mqtt with resurt code ')
    client.subscribe(REPORT_TOPIC)  # 订阅主题


def on_message(client, userdata, msg):
    """
    接收客户端发送的消息
    :param client: 连接信息
    :param userdata:
    :param msg: 客户端返回的消息
    :return:
    """
    logging.info(msg.topic + " " + ":" + str(msg.payload))
    id = msg.topic.split("/")[1]
    payload = str(msg.payload, encoding="utf-8")
    if payload == "heartbeat":
        client.publish("two/"+id, "yes", 2)
    else:
        payload = json.loads(msg.payload.decode('utf-8'))
        print(payload["op"])
        if payload["op"] == "askok":
            mysql_init()
            global id_val
            id_val += 1
            # 根据设备id查询到其在数据库中的id
            sql = "SELECT id FROM backends_deviceinfo WHERE uri='%s'" % id
            try:
                # 执行SQL语句
                cursor.execute(sql)
                # 获取所有记录列表
                results = cursor.fetchall()
                sql_id = results[0][0]
            except Exception as e:
                print(e)

            # 查询最后一条设备信息的id
            sql = "SELECT max(id) FROM backends_devicedata"
            try:
                # 执行SQL语句
                cursor.execute(sql)
                # 获取所有记录列表
                results = cursor.fetchall()
                data_id = results[0][0]
            except Exception as e:
                print(e)

            # 保存设备信息到数据库
            datetime1 = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            if data_id:
                sql = "INSERT INTO backends_devicedata(id,\
                     singal, status, lac, ci, device_id,time) \
                   VALUES (%s, '%s', '%s', '%s', '%s', '%s', str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%S'))" % \
                  (data_id+1, payload["data"]["sig"], payload["data"]["sta"], payload["data"]["LAC"], payload["data"]["CI"], sql_id, datetime1)
            else:
                sql = "INSERT INTO backends_devicedata(id,\
                                     singal, status, lac, ci, device_id,time) \
                                   VALUES (%s, '%s', '%s', '%s', '%s', '%s', str_to_date('%s','%%Y-%%m-%%d %%H:%%i:%%S'))" % \
                      (1, payload["data"]["sig"], payload["data"]["sta"], payload["data"]["LAC"],
                       payload["data"]["CI"], sql_id, datetime1)
            try:
                # 执行sql语句
                cursor.execute(sql)
                # 提交到数据库执行
                connect.commit()
            except Exception as e:
                # Rollback in case there is any error
                print(e)
                connect.rollback()
            else:
                connect.close()
        elif payload["op"] == "startok" or payload["op"] == "clearok":
            # 保存设备状态到数据库
            pass
        elif payload["op"] == "consume":
            # 保存支付状态到数据库
            pass
        else:
            pass


def server_connect(client):
    client.on_connect = on_connect  # 启用订阅模式
    client.on_message = on_message  # 接收消息

    client.username_pw_set("zskjtest/zfpcb", "c026soJRygQTc6wat8fflf2aEbG9vyDk7Rd+90yDQSW=")
    client.connect("47.105.162.90", 1883, 60)  # 链接
    # client.loop_start()   # 以start方式运行，需要启动一个守护线程，让服务端运行，否则会随主线程死亡
    client.loop_forever()  # 以forever方式阻塞运行。


def server_stop(client):
    client.loop_stop()  # 停止服务端
    sys.exit(0)

def mysql_init():
    global connect
    global cursor
    try:
        # 连接对象
        connect = MySQLdb.connect(host='47.105.162.90',  # 主机地址
                                  user='root',  # 账号
                                  password='654321',  # 密码
                                  database='MQTT_PROJECT',  # 数据库名
                                  use_unicode=True,
                                  charset='utf8')  # 指定字符集
        # 游标对象
        cursor = connect.cursor()  # 通过连接对象调用cursor()
    except Exception as e:
        print(e)
        connect.close()


def set_logging_info():
    logging.basicConfig(level=logging.INFO,  # 控制台打印的日志级别
                        filename='new.log',
                        filemode='a',  ##模式，有w和a，w就是写模式，每次都会重新写日志，覆盖之前的日志
                        # a是追加模式，默认如果不写的话，就是追加模式
                        format=
                        '%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
                        # 日志格式
                        )


def server_main():

    set_logging_info()
    client_id = time.strftime('%Y%m%d%H%M%S', time.localtime(time.time()))
    client = mqtt.Client(client_id, transport='tcp')
    # 连接数据库
    server_connect(client)


if __name__ == '__main__':
    # 启动监听
    server_main()
